﻿Imports System.Data.SqlClient
Imports sonicERPinfo
Imports System.Reflection

Public Class DAL

    Dim con As New conexao


    ''' <summary>
    ''' procedimento para update , insert, e delete no banco de dados
    ''' OBS: parameter e entidade tem que estar alinhados e estanciados mesmo se nao existir parametros
    ''' ex: Dim parameter as New ArrayList
    ''' ex: Dim entidade as New ArrayList
    ''' </summary>
    ''' <param name="select_">comando select pode ser usado no  insert, update ou delete 
    ''' ex : inset into values TABELA(coluna,)(@parametro) </param>
    ''' <param name="comando">informar se o select e do tipo procedure TRUE ou text FALSE</param>
    ''' <param name="parameter">lista dos parametros do comando  select
    '''ex: ex : inset into values TABELA(coluna,)(@parametro)
    ''' dim parameter as new arraylist
    ''' parameter.add("@parametro")</param>
    ''' <param name="entidade">lista das propriedades da classes de entidades ex:public class pai dim entidade as new pai
    ''' pai.add(entidade.nome)</param>
    ''' <param name="existeparametro">se for um select sem parametro FALSE se existirpametro TRUE</param>
    ''' <remarks></remarks>



    Public Sub SQLExecuteNonQuery(ByVal select_ As String, _
                      ByVal comando As Boolean, _
                      ByVal parameter As ArrayList, _
                      ByVal entidade As ArrayList, _
                      ByVal existeparametro As Boolean)

        Dim cn As New SqlConnection(con.conexao)
        Try
            Dim cmd As New SqlCommand
           
            cmd.Connection = cn
            cmd.CommandText = select_
            If comando = True Then
                cmd.CommandType = CommandType.StoredProcedure
            Else
                cmd.CommandType = CommandType.Text
            End If
            If existeparametro = True Then

                For index As Integer = 0 To parameter.Count - 1
                    cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)

                Next
            End If
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As SqlException
            Select Case ex.Number
                Case 547
                    Throw New Exception("Você Não Pode Excluir Porque Existe Restrições Para Cadastro")
                Case 2627
                    Throw New Exception(" Ja Existe Um Cadastro com Esse Nome Consulte sua Lista")
                Case Else
                    Throw New Exception(ex.Message)
            End Select

        End Try


    End Sub

    Public Sub SQLExecuteNonQuery(ByVal select_ As String, _
                      ByVal comando As Boolean, _
                      ByVal parameter As ArrayList, _
                      ByVal entidade As ArrayList, _
                      ByVal existeparametro As Boolean,
                      ByVal transacao As Boolean)

        Dim cn As New SqlConnection(con.conexao)
        Dim cmd As SqlCommand
        Try
            If Not transacao Then

                cmd = New SqlCommand()
            End If
            If transacao Then

                sonicERPDAL.EntidadeDal.cmd.CommandText = select_
                If comando = True Then
                    sonicERPDAL.EntidadeDal.cmd.CommandType = CommandType.StoredProcedure
                Else
                    sonicERPDAL.EntidadeDal.cmd.CommandType = CommandType.Text
                End If
                If existeparametro = True Then
                    sonicERPDAL.EntidadeDal.cmd.Parameters.Clear()
                    For index As Integer = 0 To parameter.Count - 1

                        sonicERPDAL.EntidadeDal.cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)

                    Next
                End If
            Else


                cmd.Connection = cn

                cmd.CommandText = select_
                If comando = True Then
                    cmd.CommandType = CommandType.StoredProcedure
                Else
                    cmd.CommandType = CommandType.Text
                End If
                If existeparametro = True Then

                    For index As Integer = 0 To parameter.Count - 1
                        cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)

                    Next
                End If
                cn.Open()
            End If

            If transacao Then
                
                sonicERPDAL.EntidadeDal.cmd.ExecuteNonQuery()


            Else
                cmd.ExecuteNonQuery()
                cn.Close()

            End If



        Catch ex As SqlException
            If transacao Then
                sonicERPDAL.EntidadeDal.cmd.Transaction.Rollback("teste")
            End If
            Select Case ex.Number
                Case 547
                    Throw New Exception("Você Não Pode Excluir Porque Existe Restrições Para Cadastro")
                Case 2627
                    Throw New Exception(" Ja Existe Um Cadastro com Esse Nome Consulte sua Lista")
                Case Else
                    Throw New Exception(ex.Message)
            End Select

        End Try


    End Sub

    Public Sub SQLExecuteNonQuery(ByVal select_ As String, _
                     ByVal comando As Boolean, _
                     ByVal parameter As ArrayList, _
                     ByVal entidade As ArrayList, _
                     ByVal existeparametro As Boolean,
                     ByVal transacao As Boolean,
                     ByVal salvarTransacao As Boolean)

        Dim cn As New SqlConnection(con.conexao)
        Dim cmd As SqlCommand
        Try
            If Not transacao Then

                cmd = New SqlCommand()
            End If
            If transacao Then
                sonicERPDAL.EntidadeDal.cmd.Connection = cn
                sonicERPDAL.EntidadeDal.cmd.CommandText = select_
                If comando = True Then
                    sonicERPDAL.EntidadeDal.cmd.CommandType = CommandType.StoredProcedure
                Else
                    sonicERPDAL.EntidadeDal.cmd.CommandType = CommandType.Text
                End If
                If existeparametro = True Then
                    sonicERPDAL.EntidadeDal.cmd.Parameters.Clear()
                    For index As Integer = 0 To parameter.Count - 1

                        sonicERPDAL.EntidadeDal.cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)

                    Next
                End If
            Else


                cmd.Connection = cn

                cmd.CommandText = select_
                If comando = True Then
                    cmd.CommandType = CommandType.StoredProcedure
                Else
                    cmd.CommandType = CommandType.Text
                End If
                If existeparametro = True Then

                    For index As Integer = 0 To parameter.Count - 1
                        cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)

                    Next
                End If
                cn.Open()
            End If

            If transacao Then
                If salvarTransacao Then
                    sonicERPDAL.EntidadeDal.cmd.Connection.Open()
                    sonicERPDAL.EntidadeDal.TRANS = sonicERPDAL.EntidadeDal.cmd.Connection.BeginTransaction("teste")
                    sonicERPDAL.EntidadeDal.TRANS.Save("teste")
                    sonicERPDAL.EntidadeDal.cmd.Transaction = sonicERPDAL.EntidadeDal.TRANS
                End If
                sonicERPDAL.EntidadeDal.cmd.ExecuteNonQuery()


            Else
                cmd.ExecuteNonQuery()
                cn.Close()

            End If



        Catch ex As SqlException
            If transacao Then
                sonicERPDAL.EntidadeDal.cmd.Transaction.Rollback("teste")
            End If
            Select Case ex.Number
                Case 547
                    Throw New Exception("Você Não Pode Excluir Porque Existe Restrições Para Cadastro")
                Case 2627
                    Throw New Exception(" Ja Existe Um Cadastro com Esse Nome Consulte sua Lista")
                Case Else
                    Throw New Exception(ex.Message)
            End Select

        End Try


    End Sub


    ''' <summary>
    ''' Funçao para retornar comandos select
    ''' ex: SELECT * FROM tabela
    ''' OBS: parameter e entidade tem que estar alinhados e estanciados mesmo se nao existir parametros
    ''' ex: Dim parameter as New ArrayList
    ''' ex: Dim entidade as New ArrayList
    ''' </summary>
    ''' <param name="select_">ex: SELECT * FROM tabela</param>
    ''' <param name="comando"> '''  informar se o select e do tipo procedure TRUE ou text FALSE</param>
    ''' <param name="parameter">lista dos parametros do comando  select  ex :SELECT * FROM tabela WHERE coluna = @parametro</param>
    ''' <param name="entidade"> lista das propriedades da classes de entidades ex:public class pai
    ''' dim entidade as new pai
    ''' pai.add(entidade.nome)</param>
    ''' <param name="existeparametro"> se existir parametro ex :SELECT * FROM tabela WHERE coluna = @parametro use TRUE  se nao ex :SELECT * FROM tabela  FALSE </param>
    ''' <returns>
    ''' tabela de dados
    ''' ex id,   nome,  data do cadastro
    ''' ex  1 , rogerio, 31/10/2011</returns>
    ''' <remarks></remarks>


    Public Function SqlDataAdapter(ByVal select_ As String, _
                           ByVal comando As Boolean, _
                           ByVal parameter As ArrayList, _
                           ByVal entidade As ArrayList, _
                           ByVal existeparametro As Boolean)
        Try
            Dim cn As New SqlConnection(con.conexao)
            Dim cmd As New SqlDataAdapter(select_, cn)
            If comando = True Then
                cmd.SelectCommand.CommandType = CommandType.StoredProcedure
            Else
                cmd.SelectCommand.CommandType = CommandType.Text
            End If

            If existeparametro = True Then
                For index As Integer = 0 To parameter.Count - 1
                    cmd.SelectCommand.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)
                Next


            End If

            Dim dt As New DataTable
            cmd.Fill(dt)
            Dim l As New DataView(dt)
            cn.Close()
            Return l


        Catch ex As SqlException
            Select Case ex.Number
                Case 547
                    Throw New Exception("Você Não Pode Excluir Porque Existe Restrições Para Cadastro")
                Case 2627
                    Throw New Exception("Este Cadastro Ja Existe Consulte sua Lista")
                Case Else
                    Throw New Exception(ex.Message)
            End Select
        End Try

    End Function

    Public Overloads Function SqlEcuteReader(ByVal select_ As String, _
                          ByVal comando As Boolean, _
                          ByVal parameter As ArrayList, _
                          ByVal entidade As ArrayList, _
                          ByVal existeparametro As Boolean,
                          ByVal totalcolunas As Integer,
                          ByRef obj As Object,
                           ByVal objetos As ArrayList, ByVal transacao As Boolean)
        Dim cn As New SqlConnection(con.conexao)
        Dim cmd As SqlCommand
        If Not transacao Then
            cmd = New SqlCommand()
        End If

        Try
            If transacao Then

                sonicERPDAL.EntidadeDal.cmd.Connection = cn
                sonicERPDAL.EntidadeDal.cmd.CommandText = select_
                If comando = True Then
                    sonicERPDAL.EntidadeDal.cmd.CommandType = CommandType.StoredProcedure
                Else
                    sonicERPDAL.EntidadeDal.cmd.CommandType = CommandType.Text
                End If

                If existeparametro Then
                    sonicERPDAL.EntidadeDal.cmd.Parameters.Clear()
                    For index As Integer = 0 To parameter.Count - 1
                        sonicERPDAL.EntidadeDal.cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)
                    Next
                End If
            Else

                cmd.Connection = cn
                cmd.CommandText = select_
                If comando = True Then
                    cmd.CommandType = CommandType.StoredProcedure
                Else
                    cmd.CommandType = CommandType.Text
                End If

                If existeparametro Then
                    For index As Integer = 0 To parameter.Count - 1
                        cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)
                    Next
                End If
                cn.Open()
            End If

            If transacao Then

                sonicERPDAL.EntidadeDal.cmd.Connection.Open()
                sonicERPDAL.EntidadeDal.TRANS = sonicERPDAL.EntidadeDal.cmd.Connection.BeginTransaction("teste")
                sonicERPDAL.EntidadeDal.TRANS.Save("teste")
                sonicERPDAL.EntidadeDal.cmd.Transaction = sonicERPDAL.EntidadeDal.TRANS
            End If
            Dim read As SqlDataReader

            If transacao Then
                read = sonicERPDAL.EntidadeDal.cmd.ExecuteReader()
            Else

                read = cmd.ExecuteReader()
            End If
            Dim lista As New List(Of Object)
            While read.Read
                obj = Activator.CreateInstance(obj.GetType())
                For i = 0 To totalcolunas - 1
                    Dim pi As PropertyInfo = obj.GetType().GetProperty(objetos(i).ToString())

                    Select Case pi.PropertyType.FullName.ToString()

                        Case "System.Int32"
                            pi.SetValue(obj, Convert.ToInt32(read(i)), Nothing)
                        Case "System.String"
                            pi.SetValue(obj, Convert.ToString(read(i)), Nothing)
                        Case "System.Decimal"
                            pi.SetValue(obj, Convert.ToDecimal(read(i)), Nothing)
                        Case "System.Boolean"
                            pi.SetValue(obj, Convert.ToBoolean(read(i)), Nothing)
                        Case "System.DateTime"
                            pi.SetValue(obj, Convert.ToDateTime(read(i)), Nothing)
                        Case "System.Double"
                            pi.SetValue(obj, Convert.ToDouble(read(i)), Nothing)
                        Case Else
                            If pi.PropertyType.FullName.IndexOf("System.Int32") > 0 Then
                                pi.SetValue(obj, Convert.ToInt32(read(i)), Nothing)
                            Else

                                pi.SetValue(obj, Convert.ToDateTime(read(i)), Nothing)
                            End If
                    End Select

                Next
                lista.Add(obj)
            End While

            If transacao Then
                read.Close()

            Else

                cn.Close()
                read.Close()
            End If
            Return lista
        Catch ex As SqlException
            If transacao Then
                sonicERPDAL.EntidadeDal.cmd.Transaction.Rollback("teste")
            End If
            Throw New Exception(ex.Message)
        End Try

    End Function

    Public Overloads Function SqlEcuteReader(ByVal select_ As String, _
                          ByVal comando As Boolean, _
                          ByVal parameter As ArrayList, _
                          ByVal entidade As ArrayList, _
                          ByVal existeparametro As Boolean,
                           ByRef obj As ArrayList,
                          ByVal objetos As List(Of String),
                          ByVal totalcolunas As List(Of Integer)
                        )
        Try
            Dim cn As New SqlConnection(con.conexao)
            Dim cmd As New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = select_
            If comando = True Then
                cmd.CommandType = CommandType.StoredProcedure
            Else
                cmd.CommandType = CommandType.Text
            End If

            If existeparametro Then
                For index As Integer = 0 To parameter.Count - 1
                    cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)
                Next
            End If
            cn.Open()
            Dim read As SqlDataReader = cmd.ExecuteReader()
            Dim lista As New List(Of Object)
            Dim j As Integer = 0
            Dim k As Integer = 0
            Dim proximo As Boolean = False
            Dim achou As Boolean = True
            Do

                While read.Read
                    Dim obj1 As Object
                    obj1 = obj(j)
                    obj1 = Activator.CreateInstance(obj1.GetType())
                    If j <= 0 Then
                        k = 0
                    Else
                        k = Integer.Parse(totalcolunas(j - 1).ToString())
                    End If
                    For i = 0 To Integer.Parse(totalcolunas(j).ToString) - 1
                        Dim pi As PropertyInfo = obj1.GetType().GetProperty(objetos(k).ToString())

                        Select Case pi.PropertyType.FullName.ToString()

                            Case "System.Int32"
                                pi.SetValue(obj1, Convert.ToInt32(read(i)), Nothing)
                            Case "System.String"
                                pi.SetValue(obj1, Convert.ToString(read(i)), Nothing)
                            Case "System.Decimal"
                                pi.SetValue(obj1, Convert.ToDecimal(read(i)), Nothing)
                            Case "System.Boolean"
                                pi.SetValue(obj1, Convert.ToBoolean(read(i)), Nothing)
                            Case "System.DateTime"
                                pi.SetValue(obj1, Convert.ToDateTime(read(i)), Nothing)
                            Case "System.Double"
                                pi.SetValue(obj1, Convert.ToDouble(read(i)), Nothing)
                            Case Else
                                If pi.PropertyType.FullName.IndexOf("System.Int32") > 0 Then
                                    pi.SetValue(obj1, Convert.Toint32(read(i)), Nothing)
                                Else
                                    pi.SetValue(obj1, Convert.ToDateTime(read(i)), Nothing)
                                End If

                        End Select

                        k += 1


                    Next

                    lista.Add(obj1)

                End While
                j += 1

            Loop While read.NextResult()
            cn.Close()
            read.Close()
            Return lista
        Catch ex As SqlException
            Throw New Exception(ex.Message)
        End Try

    End Function
    Public Function ExecuteScalar(ByVal select_ As String, _
                           ByVal comando As Boolean, _
                           ByVal parameter As ArrayList, _
                           ByVal entidade As ArrayList, _
                           ByVal existeparametro As Boolean)
        Try
            Dim cn As New SqlConnection(con.conexao)
            Dim cmd As New SqlCommand(select_, cn)
            If comando = True Then
                cmd.CommandType = CommandType.StoredProcedure
            Else
                cmd.CommandType = CommandType.Text
            End If

            If existeparametro = True Then
                For index As Integer = 0 To parameter.Count - 1
                    cmd.Parameters.AddWithValue(parameter(index).ToString, entidade(index).ToString)
                Next
            End If
            cn.Open()
            Dim valor As Integer = cmd.ExecuteScalar()
            cn.Close()
            Return valor
        Catch ex As SqlException

            Throw New Exception(ex.Message)

        End Try

    End Function










End Class
